/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package helpdesk;

import java.util.Date;
import java.sql.Timestamp;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * Represents a help ticket in the database.
 *
 * @author brian
 */
public class HelpTicket {

    int id;
    int userId; // creator of the ticket
    int expertiseId;
    int supportUserId;
    String supportUser;
    String expertise;
    String user;
    int priority;
    Date dateOpened;
    Date dateClosed = null;
    Timestamp modified;
    String title;
    String description;

    /**
     * Get date this ticket was closed (or null if it wasn't).
     *
     * @return
     */
    public Date getDateClosed() {
        return dateClosed;
    }

    /**
     * Get date ticket was opened. This is set when the ticket is created.
     * @return
     */
    public Date getDateOpened() {
        return dateOpened;
    }

    /**
     * Get the description of the problem.
     * @return
     */
    public String getDescription() {
        return description;
    }

    /**
     * Set the description of the problem. Does not update the DB.
     * @param description
     */
    public void setDescription(String description) {
        this.description = description;
    }

    /**
     * Get the name of the expertise this ticket corresponds to.
     * @return
     */
    public String getExpertise() {// database is read to determine this
        return expertise;
    }

    /**
     * Get the id of the expertise this ticket corresponds to.
     * @return
     */
    public int getExpertiseId() {
        return expertiseId;
    }

    /**
     * Set the id of the expertise this belongs to. Does not update the DB.
     * @param expertiseId
     */
    public void setExpertiseId(int expertiseId) {
        this.expertiseId = expertiseId;
    }

    /**
     * Get the unique id of this helpticket generated by the database.
     * @return
     */
    public int getId() {
        return id;
    }

    /**
     * Get the priority of this help ticket.
     * @return
     */
    public int getPriority() {
        return priority;
    }

    /**
     * Set the priority of this help ticket. Does not update the DB.
     * @param priority
     */
    public void setPriority(int priority) {
        this.priority = priority;
    }

    /**
     * Get the email of the user that created this ticket.
     * @return
     */
    public String getUser() { // database is read to determine this
        return user;
    }

    /**
     * Get the id of the user that created this ticket.
     * @return
     */
    public int getUserId() {
        return userId;
    }

    /**
     * Set the owner of the ticket
     * @param userId
     */
    public void setUserId(int userId) {
        this.userId = userId;
    }

    /**
     * Get the ticket's title.
     * @return
     */
    public String getTitle()
    {
        return title;
    }

    /**
     * Set the ticket's title. Does not update the DB.
     * @param title
     */
    public void setTitle(String title)
    {
        this.title = title;
    }


    /**
     * Find the help ticket that has the id id.
     *
     * @param id
     * @return
     * @throws SQLException
     */
    public static HelpTicket getById(int id) throws SQLException
    {
        HelpTicket ticket = null;
        Connection conn = null;
        Statement stmt;
        ResultSet rs;
        
        try
        {
            conn = DBManager.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT help_tickets.helpticketid, help_tickets.userid, " +
                    "help_tickets.expertiseid, expertise.name, owner.email, help_tickets.priority, " +
                    "help_tickets.datetimeopen, help_tickets.datetimeclosed, help_tickets.title, " +
                    "help_tickets.description, help_tickets.supportuserid, support.email, help_tickets.modified FROM " +
                    "help_tickets INNER JOIN users AS owner ON help_tickets.userid=owner.userid " +
                    "LEFT OUTER JOIN users AS support on support.userid=help_tickets.supportuserid " +
                    "INNER JOIN expertise ON expertise.expertiseid=help_tickets.expertiseid WHERE " +
                    "help_tickets.helpticketid=" + id);
            if (rs.next())
            {
                ticket = new HelpTicket();
                ticket.id = rs.getInt(1);
                ticket.userId = rs.getInt(2);
                ticket.expertiseId = rs.getInt(3);
                ticket.expertise = rs.getString(4);
                ticket.user = rs.getString(5);
                ticket.priority = rs.getInt(6);
                ticket.dateOpened = rs.getTimestamp(7); 
                ticket.dateClosed = rs.getTimestamp(8);
                ticket.title = rs.getString(9);
                ticket.description = rs.getString(10);
                ticket.supportUserId = rs.getInt(11);
                ticket.supportUser = rs.getString(12);
                ticket.modified = rs.getTimestamp(13);
            }
        }
        finally
        {
            if (conn != null)
                conn.close();
        }
        
        return ticket;
    }

    /**
     * Return help specific tickets into array using filters.
     *
     * TODO: implement filters
     * FIXME: change parameters
     *
     * @param numTickets
     * @param opened 
     * @param closed 
     * @param owned
     * @param notowned
     * @param accepted 
     * 
     * @return ArrayList<HelpTicket>
     * @throws SQLException
     * 
     */
    public static ArrayList<HelpTicket> getHelpTickets(int numTickets,
                                                       boolean opened,
                                                       boolean closed,
                                                       int owned,
                                                       int notowned,
                                                       boolean accepted)
                                                       throws SQLException
    {
        return getHelpTickets(numTickets);
    }

    /**
     * Return numTickets help tickets
     *
     * @return
     */
    // FIXME: allow specification of range
    public static ArrayList<HelpTicket> getHelpTickets(int numTickets) throws SQLException
    {
        ArrayList<HelpTicket> tickets = null;
        Connection conn;
        Statement stmt;
        ResultSet rs;
        int rows = 0;

        conn = DBManager.getConnection();
        try
        {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT help_tickets.helpticketid, help_tickets.userid, help_tickets.expertiseid, expertise.name, owner.email, help_tickets.priority, help_tickets.datetimeopen, help_tickets.datetimeclosed, help_tickets.title, help_tickets.description, help_tickets.supportuserid, support.email, help_tickets.modified FROM help_tickets INNER JOIN users AS owner ON help_tickets.userid=owner.userid LEFT OUTER JOIN users AS support on support.userid=help_tickets.supportuserid INNER JOIN expertise ON expertise.expertiseid=help_tickets.expertiseid ORDER BY modified DESC");

            tickets = new ArrayList<HelpTicket>(numTickets);
            while (rs.next() && rows < numTickets)
            {
                HelpTicket ticket = new HelpTicket();
                ticket.id = rs.getInt(1);
                ticket.userId = rs.getInt(2);
                ticket.expertiseId = rs.getInt(3);
                ticket.expertise = rs.getString(4);
                ticket.user = rs.getString(5);
                ticket.priority = rs.getInt(6);
                ticket.dateOpened = rs.getTimestamp(7);
                ticket.dateClosed = rs.getTimestamp(8);
                ticket.title = rs.getString(9);
                ticket.description = rs.getString(10);
                ticket.supportUserId = rs.getInt(11);
                ticket.supportUser = rs.getString(12);
                ticket.modified = rs.getTimestamp(13);
                tickets.add(ticket);
                rows++;
            }
        } finally {
            conn.close();
        }

        return tickets;
    }

    /**
     * Close the help ticket. Updates DB.
     *
     * @throws SQLException
     */
    public void close() throws SQLException {
        Connection conn = null;
        Statement stmt;
        Timestamp ts = new Timestamp(new Date().getTime());
        try {
            conn = DBManager.getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(
                    "UPDATE help_tickets SET datetimeclosed='" + ts +
                    "' WHERE helpticketid=" + id
                    );
        } finally {
            if (conn != null) conn.close();
        }
    }

    /**
     * Create ticket in DB.
     * @throws SQLException
     */
    public void create() throws SQLException
    {
        Connection conn = null;
        Date d = new Date();
        Timestamp tStamp = new Timestamp(d.getTime());
        try
        {
        
        conn = DBManager.getConnection();
        PreparedStatement ps = conn.prepareStatement("INSERT INTO help_tickets (userid, expertiseid, priority, datetimeopen, title, description, modified) VALUES (?, ?, ?, ?, ?, ?, ?)");
        ps.setInt(1, userId);
        ps.setInt(2, expertiseId);

        ps.setInt(3, priority);
        ps.setTimestamp(4, tStamp);
        ps.setString(5, title);
        ps.setString(6, description);
        ps.setTimestamp(7, tStamp);
        ps.executeUpdate();
        conn.commit();
        }
        finally
        {
            if (conn != null) conn.close();
        }
    }



    /**
     * Get the id of the support user that accepted this ticket. 0 means
     * there isn't one.
     *
     * @return
     */
    public int getSupportUserId() {
        return supportUserId;
    }

    /**
     * Will actually set this value in the database
     * @param supportUserId
     */
    public void setSupportUserId(int supportUserId) throws SQLException {
        Connection conn = null;
        Statement stmt;

        try
        {
            conn = DBManager.getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate("UPDATE help_tickets SET supportUserId=" + supportUserId + " WHERE helpticketid=" + this.id);
        }
        finally
        {
            if (conn != null)
                conn.close();
        }

        this.supportUserId = supportUserId;
    }

    
    

    
    /**
     * Get the name of the support user that has accepted this ticket.
     *
     * @return
     */
    public String getSupportUser() {
        return supportUser;
    }
 
    /**
     * Get when the last update of this ticket was created. The database
     * updates this field automatically with a trigger.
     *
     * @return
     */
    public Timestamp getModified() {
        return modified;
    }

}
